<!DOCTYPE html PUBLIC "-//W3C//DTD XHTML 1.0 Strict//EN" "http://www.w3.org/TR/xhtml1/DTD/xhtml1-strict.dtd">

<html xmlns="http://www.w3.org/1999/xhtml">
<head>
<meta http-equiv="content-type" content="text/html; charset=utf-8" />
<title>Find Managers of Contractors</title>
</head>
<!--- <cfmail to="bfrick@scf.cc" from="ContractorExpiration@scf.cc" subject="Contractor Expiration Notice - Started" type="html">
	Job Started ...
</cfmail> --->

<cfobject type="dotnet" name="objQuery" class="ADTools.Query" assembly ="C:\Program Files\Southcentral Foundation\ADTools\ADTools.dll"> 

<cfoutput>

	<cfquery name="qryGetAccounts" datasource="RosettaStone">
		scf_GetAllActiveSCFAccounts
	</cfquery>
	
	
	
	<cfquery dbtype="query" name="qryGetContractors">
		Select * from qryGetAccounts
		Where NonSCFMasterID is not NULL and UserAccount is not NULL and UserAccount Not Like 'ANMC%'
	</cfquery>
	
	<cfset managerArray = ArrayNew(1) />
	
	<cfset strManagerList = "">
	<cfloop query="qryGetContractors">
		<cfset strExpDate = objQuery.GetAccountExpirationDate(#AD_GUID#, "10.100.1.101")>
		
		<cfset intUserAccountControl = objQuery.GetProperty("useraccountcontrol", "10.100.1.101", "","#AD_GUID#","")>
		<cfif intUserAccountControl NEQ 546 AND intUserAccountControl NEQ 514>
			<cfset bolDisabled = False>
		<cfelse>
			<cfset bolDisabled = True>
		</cfif>
		
		<cfset strManagerDN = objQuery.GetProperty("manager", "10.100.1.101", "", #AD_GUID#, "")>
		#UserAccount# #strExpDate#
		<cfif isdefined("strManagerDN")>
			 #strManagerDN#
		<cfelse>
			-----------------------NO MANAGER-----------------------------
			<cfset strManagerDN ="">
			
			<!--- NOTE: Cherwell will not accept the message if there is more than one recipient specified in the 'TO' field. --->
			<cfmail to="helpdesk@scf.cc" from="ContractorExpiration@scf.cc" bcc="SCFAppNotifyAdmin@scf.cc" subject="Please Assign Contractor Supervisor" type="html">
				<p>Service Desk,<BR /></p>
								
				<p>The following contractor account is expiring soon, and no manager is listed for these account.  Please investigate (with help from HR) and assign an SCF Employee as the Manager.</p>
				
				<p>ACCOUNT TO MODIFY:<BR />
					- <strong>#UserAccount#</strong><BR />
				</p>

				<p>Please complete this ticket within 48 hours, if possible.  Thanks!</p>
				
				<hr />
				<p style="font-size:10px;">NOTE:  This is an automated message.</p>
			</cfmail>
			
		</cfif><BR />
		<cfif strExpDate NEQ "1/1/1970 12:00:00 AM" AND IsDate(strExpDate) EQ 1 and strManagerDN NEQ "" and Not bolDisabled>
			<cfif DateAdd("d", -14, strExpDate) LT Now()>
				<cfset strManagerEmail = objQuery.GetProperty("mail", "10.100.1.101", "", "", #strManagerDN#)>
				<cfif not (strManagerList Contains strManagerEmail)>
					<cfset strManagerList = strManagerList & strManagerEmail & ";">
				</cfif>
				<cfset ArrayAppend(managerArray, ListGetAt(strManagerEmail, 1, '@')) />
			<cfelse>
				<cfset ArrayAppend(managerArray, "SKIPPED") />	<!--- We don't care about these --->
			</cfif>
		<cfelse>
			<cfset ArrayAppend(managerArray, "SKIPPED") />	<!--- We don't care about these --->
		</cfif>
	</cfloop>
	
	<cfset QueryAddColumn(qryGetContractors, "managerAccount", "VarChar", managerArray) />
	
	#strManagerList#

	<cfloop list="#strManagerList#" index="strManager" delimiters=";"> 
	
		<cfset managerAccount = ListGetAt(strManager, 1, '@') />
		<cfset oldManager = "" />
		<cfset managerNotFound = false />
		
		<cfquery dbtype="query" name="IsManagerAnActiveUser">
			SELECT *
			FROM qryGetAccounts
			WHERE LOWER(UserAccount) = <cfqueryparam value="#LCase(managerAccount)#" cfsqltype="cf_sql_varchar" />
				OR LOWER(UserAccount) = <cfqueryparam value="anmc#LCase(managerAccount)#" cfsqltype="cf_sql_varchar" />
		</cfquery>
		
		<p>#managerAccount# - Active: #IIf(IsManagerAnActiveUser.RecordCount EQ 1, DE("YES"), DE("NO"))#<br />
		
		<!--- If Manager is no longer an active user, graduate any contractors to the manager's supervisor. --->
		<cfif (IsManagerAnActiveUser.RecordCount LT 1)>
			<cfset oldManager = managerAccount />
			
			<!--- This will require a few steps. --->
			
			<!--- (1) Get the former manager's GUID. --->
			<cfset oldmgrGUID = objQuery.GetProperty("objectGUID", "10.100.1.101", managerAccount, "", "")>
			<cfset strGUID = "#FormatBaseN(oldmgrGUID[4],16)##FormatBaseN(oldmgrGUID[3],16)##FormatBaseN(oldmgrGUID[2],16)##FormatBaseN(oldmgrGUID[1],16)#-#FormatBaseN(oldmgrGUID[6],16)##FormatBaseN(oldmgrGUID[5],16)#-#FormatBaseN(oldmgrGUID[8],16)##FormatBaseN(oldmgrGUID[7],16)#-#FormatBaseN(oldmgrGUID[9],16)##FormatBaseN(oldmgrGUID[10],16)#-#FormatBaseN(oldmgrGUID[11],16)##FormatBaseN(oldmgrGUID[12],16)##FormatBaseN(oldmgrGUID[13],16)##FormatBaseN(oldmgrGUID[14],16)##FormatBaseN(oldmgrGUID[15],16)##FormatBaseN(oldmgrGUID[16],16)#" />
			
			<!--- (2) Get the former manager's RosettaID --->
			<cfquery datasource="SCF_General" name="qryOldManager">
				SELECT *
				FROM scf.dbo.func_GetUsers(1,1,1)
				WHERE [AD_GUID] = <cfqueryparam value="#strGUID#" cfsqltype="cf_sql_varchar" />
			</cfquery>
			
			<cfif qryOldManager.RecordCount GT 0>
			
				<!--- (3) Get the former manager's supervisor --->
				<cfstoredproc datasource="SCF_General" procedure="scf_whostheboss">
					<cfprocparam dbvarname="pintRosettaID" value="#qryOldManager.RosettaID#" cfsqltype="cf_sql_integer" />
					<cfprocresult resultset="1" name="qryNewManager" />
				</cfstoredproc>
				
				<cfif qryNewManager.RecordCount GT 0>
					<!--- (4) Set the strManager variable. --->
					<cfset strManager = qryNewManager.EMail />
					
				<cfelse>	
					<cfset managerNotFound = true />
				</cfif>				
			<cfelse>
				<cfset managerNotFound = true />
			</cfif>		
					
			<!--- (5) For each contractor, assign the contractor to the new manager. --->
			<cfquery dbtype="query" name="qryContractorsToReassign">
				SELECT *
				FROM qryGetContractors
				WHERE managerAccount = <cfqueryparam value="#oldManager#" cfsqltype="cf_sql_varchar" />
			</cfquery>
		
			<!--- NOTE: Cherwell will not accept the message if there is more than one recipient specified in the 'TO' field. --->
			<cfmail to="helpdesk@scf.cc" from="ContractorExpiration@scf.cc" bcc="SCFAppNotifyAdmin@scf.cc" subject="Please Assign New Contractor Supervisor" type="html">
				<p>Service Desk,<BR /></p>
								
				<p>The following contractor account(s) are expiring soon, and the manager listed for these accounts needs to be changed.  The former manager was <span style="color:##900; font-weight: bold;">#oldManager#</span>.</p>
				
				<p>
					NEW MANAGER: <strong>#IIf(managerNotFound, DE("Unknown"), DE("#ListGetAt(strManager, 1, '@')#"))#</strong>
				</p>
				
				<p>ACCOUNTS TO MODIFY:<BR />
					<cfloop query="qryContractorsToReassign">
						- <strong>#qryContractorsToReassign.UserAccount#</strong><BR />
					</cfloop>
				</p>

				<p>Please complete this ticket within 48 hours, if possible.  Thanks!</p>
				
				<hr />
				<p style="font-size:10px;">NOTE:  This is an automated message.</p>
			</cfmail>
			
		</cfif>
		
		<cfif !(managerNotFound) >
	
			<cfmail to="#strManager#" bcc="dmlewis@scf.cc" from="ContractorExpiration@scf.cc" subject="Contractor Expiration Notice" type="html">
	
				<cfif (IsManagerAnActiveUser.RecordCount LT 1)>
					<h3 style="color:##900;">Please Note:</h3>
					<p style="color:##900;">You are receiving this message because the account listed as the manager for one or more contractors, #oldManager#, is no longer active.  You are listed as the supervisor for that previous account, so the contractor will be updated by SCF's Service Desk to report to you.  Please wait approximately 48 hours, then try the steps below.  If you have any questions or updates, please contact SCF ITS or SCF Human Resources.</p>
				</cfif>
			
				<p>ATTN Contractor Supervisor:<BR /><BR /></p>
				
				<p>All contractor network accounts are subject to a 30 day expiration period.  Contractor accounts will expire at the end of every month and can only be extended per requests made by their direct supervisor.  Our records indicate that you are the direct supervisor of one or more contractors.  Furthermore, it appears one or more of those accounts are about to expire. <BR /><BR /></p>
	
				Instructions:<BR />
				<table>
					<tr>
						<td>
							&nbsp;&nbsp;
						</td>
						<td valign="top">
							1)
						</td>
						<td>
							&nbsp;&nbsp;
						</td>
						<td><!--- Can't be dynamically referenced if the scheduled page is going to be on the ITS site--->
							Please visit the following page: <!---a href="#CGI.SERVER_NAME#/EPAR/Non-SCF/Expiration/Review.cfm">#CGI.SERVER_NAME#/EPAR/Non-SCF/Expiration/Review.cfm</a--->
							<a href="http://www.scfonline.net/EPAR/Non-SCF/Expiration/Review.cfm">www.scfonline.net/EPAR/Non-SCF/Expiration/Review.cfm</a>
						</td>
					</tr>
					<tr>
						<td>
							&nbsp;&nbsp;
						</td>
						<td valign="top">
							2)
						</td>
						<td>
							&nbsp;&nbsp;
						</td>
						<td>
							Review the list.&nbsp;&nbsp;If a contractor under your supervision does not appear in this list, please contact the Service Desk at x5200.&nbsp;&nbsp;Likewise, if there are contractors in the list that are <strong>not</strong> under your supervision, please call x5200 to have the supervisor corrected.
						</td>
					</tr>
					<tr>
						<td>
							&nbsp;&nbsp;
						</td>
						<td valign="top">
							3)
						</td>
						<td>
							&nbsp;&nbsp;
						</td>
						<td>
							For contractors that are still actively working for SCF, select the "Extend" option.&nbsp;&nbsp;This will extend their expiration date for one month.&nbsp;&nbsp;For those that are no longer at SCF, please select the "Terminate" option.&nbsp;&nbsp;This will send a request to the Service Desk to have the account archived.
						</td>
					</tr>
					<tr>
						<td>
							&nbsp;&nbsp;
						</td>
						<td valign="top">
							4)
						</td>
						<td>
							&nbsp;&nbsp;
						</td>
						<td>
							Click the "Submit" button.
						</td>
					</tr>
				</table>
				<BR />
				This is an automated message and the mailbox is not monitored.  If you have any questions about this message, please contact the service desk at x5200. 
			</cfmail>
		</cfif>
	</cfloop>
</cfoutput>

</html>

